package com.xxx.mapper;

import com.xxx.eneity.Team;
import com.xxx.eneity.noticeStatus;
import com.xxx.out.TeamInfo;
import com.xxx.out.TeamMember;
import com.xxx.out.TeamNotice;
import com.xxx.vo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TeamMapper {

    @Select("select count(*) from t_team where name = #{name} and contest_id = #{contestId} and is_deleted = 0")
    public int isTeamNameExist(@Param("name") String name, @Param("contestId") Integer contestId);

    @Select("select count(*) from t_team where name = #{name} and contest_id = #{contestId} and id != #{teamId} and is_deleted = 0")
    public int isTeamNameExistX(@Param("name") String name, @Param("contestId") Integer contestId, @Param("teamId") Integer teamId);

    @Insert("insert into t_team (name, contest_id, info, demand) values (#{name}, #{contestId}, #{info}, #{demand})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    public int createTeam(CreateTeamVo createTeamVo);


    @Insert("insert into t_team_member (team_id, user_id, user_name, role, status, join_note) values (#{teamId}, #{userId}, #{name}, 0, 1, '队伍创始人')")
    public int addCaptain(@Param("teamId") int teamId, @Param("userId") Integer userId, @Param("name") String name);

    @Select("select * from t_team where id = #{teamId} and is_deleted = 0")
    public Team getTeamById(int teamId);

    @Select("select * from " +
            "(select team_id as teamId from t_team_member where user_id = #{userId} and status = 1 and role = 1) as a " +
            "inner join " +
            "(select id, name as teamName, contest_id as contestId, info as teamInfo, demand as teamDemand from t_team where is_deleted = 0) as b " +
            "on a.teamId = b.id " +
            "left join " +
            "(select team_id, user_id as captainId, user_name as captainName from t_team_member where role = 0 ) as d " +
            "on d.team_id = a.teamId " +
            "left join " +
            "(select id, name as contestName from t_contest) as c " +
            "on b.contestId = c.id ")
    public List<TeamInfo> getTeamList(Integer userId);

    @Select("select * from " +
            "(select team_id as teamId, user_id as captainId, user_name as captainName from t_team_member where user_id = #{userId} and role = 0) as a " +
            "inner join " +
            "(select id, name as teamName, contest_id as contestId, info as teamInfo, demand as teamDemand from t_team where is_deleted = 0) as b " +
            "on a.teamId = b.id " +
            "inner join " +
            "(select id, name as contestName from t_contest) as c " +
            "on b.contestId = c.id ")
    public List<TeamInfo> getTeamListAsCaptain(Integer userId);

    @Insert("insert into t_team_member (team_id, user_id, user_name, role, status, join_note) values " +
            "(#{teamId}, #{userId}, #{userName}, 1, 0, #{joinNote})")
    public int addTeamMember(JoinTeamVo joinTeamVo);

    @Select("select count(*) from " +
            "(select id as teamId from t_team where contest_id = #{contestId} and is_deleted = 0) as a " +
            "inner join " +
            "(select team_id from t_team_member where user_id = #{userId} and status != 2) as b " +
            "on a.teamId = b.team_id")
    public int isMemberExist(@Param("contestId") Integer contestId, @Param("userId") Integer userId);

    @Select("select contest_id from t_team where id = #{teamId} and is_deleted = 0")
    public Integer getContestId(Integer teamId);

    @Select("select * from " +
            "(select id as teamId, name as teamName, contest_id as contestId, info as teamInfo, demand as teamDemand  from t_team where id = #{teamId} and is_deleted = 0) as a " +
            "left join " +
            "(select team_id, user_id as captainId, user_name as captainName from t_team_member where role = 0) as b " +
            "on a.teamId = b.team_id " +
            "left join " +
            "(select id, name as contestName from t_contest) as c " +
            "on a.contestId = c.id ")
    public TeamInfo getTeamInfo(Integer teamId);

    @Select("select user_id as userId, user_name as username, join_note as joinNote from t_team_member where team_id = #{teamId} and role = 1 and status = 1")
    public List<TeamMember> getTeamMembers(Integer teamId);

    @Select("select user_id as userId, user_name as username, join_note as joinNote from t_team_member where team_id = #{teamId} and role = 1 and status = 0")
    public List<TeamMember> getJoinMembers(Integer teamId);

    @Update("<script>" +
            "<if test='advice == true'>" +
            "update t_team_member set status = 1 where user_id = #{userId} and team_id = #{teamId}" +
            "</if>" +
            "<if test='advice == false'>" +
            "update t_team_member set status = 2 where user_id = #{userId} and team_id = #{teamId}" +
            "</if>" +
            "</script>")
    public int reviewJoinRequest(@Param("userId") Integer userId, @Param("advice") Boolean advice, @Param("teamId") Integer teamId);

    @Insert("insert into t_team_notice (team_id, title, notice, insert_time, update_time) values (#{teamId}, #{title}, #{notice}, now(), now())")
    public int addTeamNotice(TeamNoticeVo teamNoticeVo);

    @Update("update t_team_notice set title = #{title}, notice = #{notice}, update_time = now() where id = #{noticeId}")
    public int editTeamNotice(EditNoticeVo editNoticeVo);

    @Delete("delete from t_team_notice where id = #{noticeId}")
    public int removeTeamNotice(Integer noticeId);

    @Select("select id as noticeId, title, notice, insert_time as insertDate, update_time as updateDate  from t_team_notice where team_id = #{teamId}")
    public List<TeamNotice> getTeamNoticeList(Integer teamId);

    @Select("select * from " +
            "(select id as noticeId, title, notice, insert_time as insertDate, update_time as updateDate  from t_team_notice where team_id = #{teamId}) as a " +
            "left join " +
            "(select count(*) as status, team_notice_id from t_notice_status where user_id = #{userId} group by team_notice_id) as b " +
            "on a.noticeId = b.team_notice_id")
    public List<TeamNotice> getTeamNoticeStatusList(Integer teamId, Integer userId);

    @Select("<script>" +
            "select * from " +
            "(select * from " +
            "(select id as teamId, contest_id as contestId, name as teamName, info as teamInfo, demand as teamDemand  from t_team where 1=1 " +
            "<if test = 'contestId != null'> " +
            "and contest_id = #{contestId} " +
            "</if>" +
            "and is_deleted = 0) as a " +
            "left join " +
            "(select team_id, user_id as captainId, user_name as captainName from t_team_member where role = 0 ) as b " +
            "on a.teamId = b.team_id) as c " +
            "left join " +
            "(select id, name as contestName from t_contest) as d " +
            "on c.contestId = d.id" +
            "</script>")
    public List<TeamInfo> getTeamInfoList(Integer contestId);

    @Update("update t_team set is_deleted = 1 where id = #{teamId}")
    public int removeTeam(Integer teamId);

    @Update("update t_team_member set status = #{advice} where team_id =#{teamId} and user_id = #{userId}")
    int reviewApplication(@Param(value = "advice")Integer advice,
                          @Param(value = "teamId")Integer teamId,
                          @Param(value = "userId")Integer userId);

    @Select("select teamId, teamName from " +
            "((select id as teamId, name as teamName from t_team where contest_id = #{contestId}) as a " +
            "inner join " +
            "(select team_id,user_id from t_team_member where user_id = #{userId}) as b " +
            "on a.teamId = b.team_id)")
    public TeamInfo getTeamName(Integer contestId, Integer userId);

    @Delete("delete from t_team_member where team_id = #{teamId} and user_id = #{userId}")
    public Integer exitTeam(Integer teamId, Integer userId);

    @Insert("insert into t_notice_status (user_id, team_notice_id) values (#{userId}, #{noticeId})")
    public int readTeamNotice(Integer userId, Integer noticeId);

    @Select("select count(*) from t_notice_status where user_id = #{userId} and team_notice_id = #{noticeId}")
    public int isReadTeamNotice(Integer userId, Integer noticeId);

    @Select("select team_id from t_team_notice where id = #{noticeId}")
    public Integer getTeamId(Integer noticeId);

    @Select("select username, if(count>0, '已读', '未读') as status from " +
            "(select user_name as username, user_id from t_team_member where team_id = #{teamId} and role=1) as a " +
            "left join " +
            "(select count(*) as count, user_id from t_notice_status where team_notice_id = #{noticeId} group by user_id) as b " +
            "on a.user_id = b.user_id")
    public List<noticeStatus> getReadStatus(Integer noticeId, Integer teamId);

    @Update("update t_team set name = #{teamName}, info = #{teamInfo}, demand = #{teamDemand} where id = #{teamId}")
    public int editTeam(EditTeamVo editTeamVo);

    @Delete("delete from t_team_member where team_id = #{teamId} and user_id = #{userId}")
    public int kickOutMember(Integer userId, Integer teamId);

    @Select("select count(*) from t_team_member where team_id = #{teamId} and user_id = #{userId} and role = 0")
     public int isCaptain(Integer teamId, Integer userId);

    @Update("update t_team_member set role = 1 where team_id = #{teamId} and role = 0")
    public void deleteCaptain(Integer teamId);

    @Update("update t_team_member set role = 0 where team_id = #{teamId} and user_id = #{userId}")
    public int updateCaptain(Integer teamId, Integer userId);

    @Select("select id, name  from " +
            "(select id as aid from t_team where contest_id = #{contestId}) as a " +
            "inner join " +
            "(select user_id as id, user_name as name, team_id  from t_team_member) as b " +
            "on a.aid = b.team_id")
    public List<UserVo> getTeamMemberList(Integer contestId);

    @Insert("insert into t_team_member (user_Id, user_name, team_id, role, status, join_note) values (#{userId}, #{username}, #{teamId}, 1, 1, '管理员添加的成员')")
    public int addMember(Integer userId, Integer teamId, String username);
}
